/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package almacen;

import resources.jcProperties;
import resources.local_connection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.ImageIcon;
import java.util.Vector;
import javax.swing.text.DefaultCaret;

/**
 *
 * @author Homeroe
 */
public class SyncTienda extends javax.swing.JFrame {

    /**
     * Creates new form SyncTienda
     */
    Connection cnl;
    PreparedStatement psl, precio, cantidad, categoria;
    ResultSet rsl, precior, cantidadr, existe, categoriar, key;
    Connection cnr;
    int idcategoria, sortorder;
    PreparedStatement psr = null;
    int cantidadi = 0;
    float preciof = 0.0f;
    boolean categorizar = false;
    boolean precios = false;
    boolean nuevos = false;
    boolean stock = false;
    boolean fotos = false;
    boolean propiedades = false;
    Vector id = new Vector();
    String sufijo="";

    public SyncTienda() {
        initComponents();
DefaultCaret caret = (DefaultCaret)textLog.getCaret();
caret.setUpdatePolicy(DefaultCaret.ALWAYS_UPDATE);
        Properties propiedades = new Properties();
        jcProperties editarPropiedades = new jcProperties();
        propiedades = editarPropiedades.getProperties();
        sufijo = propiedades.getProperty("sufijo");
        textDBactual.setText("Servidor Actual: " + propiedades.getProperty("serverLocal") + "(" + propiedades.getProperty("baseDatosLocal") + ")");
        textDBremoto.setText("Servidor Remoto: " + propiedades.getProperty("serverRemoto") + "(" + propiedades.getProperty("baseDatosRemoto") + ")");
        try {
            new local_connection();
            cnl = local_connection.getconnect();
            cnr = local_connection.getconnect("remote");
            PreparedStatement ps = local_connection.getconnect().prepareStatement("select id, nombre from sucursales");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                jComboBox1.addItem(rs.getObject("nombre"));
                id.addElement(rs.getInt("id"));
            }
            ps = local_connection.getconnect().prepareStatement("select * from actualizacionweb order by fecha desc limit 1");
            rs = ps.executeQuery();
            if (rs.next()) {
                textActualizacion.setText("Ultima actualizacion: " + rs.getString("fecha"));
                bfoto.setSelected(rs.getBoolean("fotos"));
                bprecio.setSelected(rs.getBoolean("precios"));
                bcategoria.setSelected(rs.getBoolean("categorias"));
                bstock.setSelected(rs.getBoolean("stock"));
                bnuevo.setSelected(rs.getBoolean("nuevos"));
                bpropiedad.setSelected(rs.getBoolean("propiedades"));
            }
        } catch (SQLException | ClassNotFoundException ex) {
Almacen.logger.log(Level.SEVERE, null, ex);        }

    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        buttonActualizar = new javax.swing.JButton();
        textActualizacion = new javax.swing.JLabel();
        buttonCancel = new javax.swing.JButton();
        textDesc = new javax.swing.JLabel();
        textDBactual = new javax.swing.JLabel();
        textDBremoto = new javax.swing.JLabel();
        jScrollPane1 = new javax.swing.JScrollPane();
        textLog = new javax.swing.JTextArea();
        bprecio = new javax.swing.JCheckBox();
        bstock = new javax.swing.JCheckBox();
        bpropiedad = new javax.swing.JCheckBox();
        bcategoria = new javax.swing.JCheckBox();
        bnuevo = new javax.swing.JCheckBox();
        bfoto = new javax.swing.JCheckBox();
        jPanel1 = new javax.swing.JPanel();
        jLabel1 = new javax.swing.JLabel();
        jComboBox1 = new javax.swing.JComboBox();

        setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);
        setTitle("Syncronizar");
        setIconImage(new ImageIcon(getClass().getResource("/resources/logo_rayito.png")).getImage());
        setResizable(false);

        buttonActualizar.setText("Actualizar");
        buttonActualizar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                buttonActualizarActionPerformed(evt);
            }
        });

        textActualizacion.setText("Ultima actualizacion:");

        buttonCancel.setText("Cerrar");
        buttonCancel.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                buttonCancelActionPerformed(evt);
            }
        });

        textDesc.setText("Actualiza el stock de la base de datos remota con la actual.");

        textDBactual.setFont(new java.awt.Font("Tahoma", 1, 11)); // NOI18N
        textDBactual.setText("Servidor Actual:");

        textDBremoto.setFont(new java.awt.Font("Tahoma", 1, 11)); // NOI18N
        textDBremoto.setText("Servidor Remoto:");

        textLog.setEditable(false);
        textLog.setColumns(20);
        textLog.setRows(5);
        jScrollPane1.setViewportView(textLog);

        bprecio.setText("Mayoreos");

        bstock.setText("Stock");

        bpropiedad.setText("Propiedades");

        bcategoria.setText("Categorias");

        bnuevo.setText("Productos Nuevos");

        bfoto.setText("Fotos");

        jPanel1.setBorder(javax.swing.BorderFactory.createEtchedBorder());

        jLabel1.setText("Precios de la sucursal");

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel1Layout.createSequentialGroup()
                        .addGap(11, 11, 11)
                        .addComponent(jLabel1))
                    .addGroup(jPanel1Layout.createSequentialGroup()
                        .addContainerGap()
                        .addComponent(jComboBox1, javax.swing.GroupLayout.PREFERRED_SIZE, 103, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap())
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addComponent(jLabel1)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jComboBox1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap())
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane1)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(textDesc)
                            .addComponent(textDBactual)
                            .addComponent(textActualizacion)
                            .addComponent(textDBremoto)
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(buttonCancel, javax.swing.GroupLayout.PREFERRED_SIZE, 73, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(buttonActualizar))
                            .addGroup(layout.createSequentialGroup()
                                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                    .addComponent(bprecio)
                                    .addComponent(bstock))
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                    .addComponent(bpropiedad)
                                    .addComponent(bcategoria))
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                    .addComponent(bfoto)
                                    .addComponent(bnuevo))
                                .addGap(18, 18, 18)
                                .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                        .addGap(0, 0, Short.MAX_VALUE)))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(textDesc)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(textDBactual)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(textDBremoto)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(textActualizacion)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(bprecio)
                            .addComponent(bpropiedad)
                            .addComponent(bnuevo))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(bstock)
                            .addComponent(bcategoria)
                            .addComponent(bfoto)))
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(0, 0, Short.MAX_VALUE)))
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(buttonCancel)
                    .addComponent(buttonActualizar))
                .addContainerGap())
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void buttonActualizarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_buttonActualizarActionPerformed
        categorizar = bcategoria.isSelected();
        precios = bprecio.isSelected();
        nuevos = bnuevo.isSelected();
        stock = bstock.isSelected();
        fotos = bfoto.isSelected();
        propiedades = bpropiedad.isSelected();
        textLog.append("Iniciando Actualizacion");
       Thread cosa = new Thread() {
            public void run(){
                try {
                psl = (PreparedStatement) cnl.prepareStatement("Select * from productos");
                rsl = psl.executeQuery();
                int i = 0;
                if (categorizar) {
                    crearCategorias();
                    categoria = cnr.prepareStatement("delete from "+sufijo+"product_to_category");
                    categoria.execute();
                }
                if(precios){
                    psr = (PreparedStatement) cnr.prepareStatement("delete from "+sufijo+"product_discount");
                    psr.execute();
                }
                while (rsl.next()) {
                    if (nuevos) {
                        psr = cnr.prepareStatement("select 1 from "+sufijo+"product where sku = ?");
                        psr.setInt(1, rsl.getInt("id_producto"));
                        existe = psr.executeQuery();
                        if (!existe.next()) {//productos nuevoss
                            crearProductos();
                        }
                    }
                    
                    if (categorizar) {//categorizar
                        categoria = cnl.prepareStatement("select nombre, id_producto from categorias_productos join categorias on categorias_productos.id_categoria=categorias.id_categoria where categorias_productos.id_producto = ?");
                        categoria.setInt(1, rsl.getInt("id_producto"));
                        categoriar = categoria.executeQuery();

                        while (categoriar.next()) {

                            categoria = cnr.prepareStatement("insert into "+sufijo+"product_to_category values((select product_id from "+sufijo+"product where sku = ?),(select category_id from "+sufijo+"category_description where name like ? limit 1))");
                            categoria.setInt(1, categoriar.getInt("id_producto"));
                            categoria.setString(2, categoriar.getString("nombre"));
                            categoria.execute();
                            textLog.append("\nse categorizo el producto numero " + categoriar.getString("id_producto"));
                        }
                    }
                    if (stock) {//stock
                        cantidad = cnl.prepareStatement("select sum(cantidad) from stock where id_producto = ?");
                        cantidad.setInt(1, rsl.getInt("id_producto"));
                        cantidadr = cantidad.executeQuery();
                        cantidadi = (cantidadr.next()) ? cantidadr.getInt(1) : 0;
                        precio = cnl.prepareStatement("select precio from precios where sucursal=? and id_producto = ? order by cantidad asc limit 1");
                        precio.setInt(1, (int) id.get(jComboBox1.getSelectedIndex()));
                        precio.setInt(2, rsl.getInt("id_producto"));
                        precior = precio.executeQuery();
                        preciof = (precior.next()) ? precior.getFloat(1) : 0.0f;
                        psr = (PreparedStatement) cnr.prepareStatement("Update "+sufijo+"product set quantity = ?, price = ?,status = ? where sku = ? ");
                        psr.setInt(1, cantidadi);
                        psr.setFloat(2, preciof);
                        psr.setInt(4, rsl.getInt("id_producto"));
                        psr.setInt(3, rsl.getInt("activo"));
                        psr.execute();
                        textLog.append("\nse actualizo el stock del producto " + rsl.getInt("id_producto") + " con la cantidad " + cantidadi + " y el precio " + preciof);
                    }
                    i++;
                    if (propiedades) {//descripcion
                        psr = (PreparedStatement) cnr.prepareStatement("update "+sufijo+"product_description set name = ?, description = ? where product_id = (select product_id from "+sufijo+"product where sku = ?)");
                        psr.setString(1, rsl.getString("nombre"));
                        psr.setString(2, (rsl.getString("especificaciones") == null) ? "" : rsl.getString("especificaciones"));
                        psr.setInt(3, rsl.getInt("id_producto"));
                        psr.execute();
                        textLog.append("\nse actualizaron las propiedades del producto " + rsl.getInt("id_producto") + " " + rsl.getString("nombre"));
                    }
                    if(precios){
                        agregarPrecios();
                    }
                    
                }
                psl=local_connection.getconnect().prepareStatement("insert into actualizacionweb values(null,?,?,?,?,?,?)");
                    psl.setBoolean(1, bprecio.isSelected());
                    psl.setBoolean(2, bstock.isSelected());
                    psl.setBoolean(3, bcategoria.isSelected());
                    psl.setBoolean(4, bfoto.isSelected());
                    psl.setBoolean(5, bnuevo.isSelected());
                    psl.setBoolean(6, bpropiedad.isSelected());
                    psl.execute();
            } catch (SQLException ex) {
Almacen.logger.log(Level.SEVERE, null, ex);                System.out.println(psr.toString());
            }
                
                textLog.append("\nACTUALIZACION TERMINADA");
       }    
            
        };
            cosa.start();
        

    }//GEN-LAST:event_buttonActualizarActionPerformed
    void agregarPrecios(){
        try {
            precio = cnl.prepareStatement("select precio,cantidad from precios where sucursal=? and id_producto = ?");
            precio.setInt(1, (int) id.get(jComboBox1.getSelectedIndex()));
                        precio.setInt(2, rsl.getInt("id_producto"));
                        precior = precio.executeQuery();
                        
                        psr = (PreparedStatement) cnr.prepareStatement("insert into "+sufijo+"product_discount values(null,(select product_id from "+sufijo+"product where sku = ?),1,?,0,?,'0000-00-00','0000-00-00')");
                        while(precior.next()){
                            psr.setInt(1, rsl.getInt("id_producto"));
                            psr.setInt(2, precior.getInt("cantidad"));
                            psr.setFloat(3, precior.getFloat("precio"));
                            psr.execute();
                        }
        } catch (SQLException ex) {
Almacen.logger.log(Level.SEVERE, null, ex);        }
                        
    }
    void crearProductos() {
        try {
            psr = cnr.prepareStatement("insert into "+sufijo+"product(model,sku,stock_status_id,manufacturer_id,shipping,date_available,weight,weight_class_id,length_class_id,sort_order,status,date_added,upc,ean,jan,isbn,mpn,location,quantity,points,tax_class_id,viewed)\n"
                    + "values(?,?,8,0,1,current_date,?,2,2,1,1,current_timestamp,'','','','','','',0,0,0,0)");
            psr.setInt(1, rsl.getInt("id_producto"));
            psr.setInt(2, rsl.getInt("id_producto"));
            psr.setInt(3, rsl.getInt("peso"));
            psr.execute();
            psr = cnr.prepareStatement("insert into "+sufijo+"product_description(product_id,language_id,name,description,meta_description,meta_keyword,tag)\n"
                    + "values((select product_id from "+sufijo+"product where sku = ?),1,?,?,?,?,?)");
            psr.setInt(1, rsl.getInt("id_producto"));
            psr.setString(2, rsl.getString("nombre"));
            String desripcion = (rsl.getString("especificaciones")!=null)?rsl.getString("especificaciones"):"";
            psr.setString(3, desripcion);
            psr.setString(4, rsl.getString("nombre"));
            psr.setString(5, rsl.getString("nombre"));
            psr.setString(6, rsl.getString("nombre"));
            psr.execute();
            psr = cnr.prepareStatement("insert into "+sufijo+"product_description(product_id,language_id,name,description,meta_description,meta_keyword,tag)\n"
                    + "values((select product_id from "+sufijo+"product where sku = ?),2,?,?,?,?,?)");
            psr.setInt(1, rsl.getInt("id_producto"));
            psr.setString(2, rsl.getString("nombre"));
            psr.setString(3, desripcion);
            psr.setString(4, rsl.getString("nombre"));
            psr.setString(5, rsl.getString("nombre"));
            psr.setString(6, rsl.getString("nombre"));
            psr.execute();
            psr = cnr.prepareStatement("insert into "+sufijo+"product_to_store select product_id,0 from "+sufijo+"product where product_id not in (select product_id from "+sufijo+"product_to_store)");
            psr.execute();
            psr=cnr.prepareStatement("insert into "+sufijo+"url_alias(query,keyword) values((select concat('product_id=',?)),?)");
            psr.setInt(1, rsl.getInt("id_producto"));
            psr.setString(2, rsl.getString("id_producto"));
            psr.execute();
            textLog.append("\nse agrego el producto " + rsl.getInt("id_producto") + " " + rsl.getString("nombre"));
        } catch (SQLException ex) {
Almacen.logger.log(Level.SEVERE, null, ex);        }
    }

    void crearCategorias() {
        try {
            categoria = cnl.prepareStatement("select nombre, descripcion from categorias");
            categoriar = categoria.executeQuery();
            while (categoriar.next()) {//crea categorias nuevas
                categoria = cnr.prepareStatement("select 1 from "+sufijo+"category_description where name like ?");
                categoria.setString(1, categoriar.getString("nombre"));
                existe = categoria.executeQuery();
                if (!existe.next()) {
                    categoria = cnr.prepareStatement("select sort_order from "+sufijo+"category order by sort_order desc limit 1");
                    key = categoria.executeQuery();
                    key.next();
                    sortorder = key.getInt(1) + 1;
                    categoria = cnr.prepareStatement("insert into "+sufijo+"category values(null,'',0,1,1,?,1,current_timestamp,current_timestamp)", Statement.RETURN_GENERATED_KEYS);
                    categoria.setInt(1, sortorder);
                    categoria.executeUpdate();
                    key = categoria.getGeneratedKeys();
                    key.next();
                    idcategoria = key.getInt(1);
                    categoria = cnr.prepareStatement("insert into "+sufijo+"category_description values(?,1,?,?,'','')");
                    categoria.setInt(1, idcategoria);
                    categoria.setString(2, categoriar.getString("nombre"));
                    categoria.setString(3, (categoriar.getString("descripcion") == null) ? "" : categoriar.getString("descripcion"));
                    categoria.execute();
                    categoria = cnr.prepareStatement("insert into "+sufijo+"category_description values(?,2,?,?,'','')");
                    categoria.setInt(1, idcategoria);
                    categoria.setString(2, categoriar.getString("nombre"));
                    categoria.setString(3, (categoriar.getString("descripcion") == null) ? "" : categoriar.getString("descripcion"));
                    categoria.execute();
                    categoria = cnr.prepareStatement("insert into "+sufijo+"category_path values(?,?,0)");
                    categoria.setInt(1, idcategoria);
                    categoria.setInt(2, idcategoria);
                    categoria.execute();
                    categoria = cnr.prepareStatement("insert into "+sufijo+"category_to_store values(?,0)");
                    categoria.setInt(1, idcategoria);
                    categoria.execute();
                    textLog.append("\nse agrego la categoria " + categoriar.getString("nombre"));
                }
            }
        } catch (SQLException ex) {
Almacen.logger.log(Level.SEVERE, null, ex);        }
    }
    private void buttonCancelActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_buttonCancelActionPerformed
        this.dispose();
    }//GEN-LAST:event_buttonCancelActionPerformed
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JCheckBox bcategoria;
    private javax.swing.JCheckBox bfoto;
    private javax.swing.JCheckBox bnuevo;
    private javax.swing.JCheckBox bprecio;
    private javax.swing.JCheckBox bpropiedad;
    private javax.swing.JCheckBox bstock;
    private javax.swing.JButton buttonActualizar;
    private javax.swing.JButton buttonCancel;
    private javax.swing.JComboBox jComboBox1;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JLabel textActualizacion;
    private javax.swing.JLabel textDBactual;
    private javax.swing.JLabel textDBremoto;
    private javax.swing.JLabel textDesc;
    private javax.swing.JTextArea textLog;
    // End of variables declaration//GEN-END:variables
}
